#-*- coding: utf-8 -*-
#@File    : getExcel.PY
#@Time    : 2020/12/4  15:29
#@Software: PyCharm

import xlrd
from xlutils.copy import copy
import json

# #1 打开excel表
# def get_excelData(sheetName,startRow,endRow):
#
#     resList = []
#     #1 表的路径
#     excelDir = '../Data/外卖系统接口测试用例.xls'
#
#     #2 打开excel对象 formatting_ino=True
#     workBook = xlrd.open_workbook(excelDir,formatting_info=True)
#
#     #3 获取一个指定的表
#     workSheet = workBook.sheet_by_name(sheetName)
#
#     #4  循坏指定的列表
#     for one in range(startRow-1,endRow):
#         reqBody = workSheet.cell(one,9).value #请求body
#         resbody = workSheet.cell(one,11).value #响应数据
#         resList.append((reqBody,resbody))
#     return resList
# if __name__ == '__main__':
#
#     for i in get_excelData('登录模块',3,7):  # 最后一个不会循坏
#         print(i)



import xlrd

def gett_ExcalData(SheetName,startrrow,endrow):
    resList = []
    filedir = '../data/外卖系统接口测试用例.xls'
    openfile = xlrd.open_workbook(filedir,formatting_info=True)
    sheetname  = openfile.sheet_by_name(SheetName)
    for i in range(startrrow-1,endrow):
        reqbody = sheetname.cell(i,9).value
        resbody = sheetname.cell(i,11).value
        resList.append((reqbody,resbody))
    return resList

def get_ExcelData2(SheetName,casename):
    filedir = '../data/外卖系统接口测试用例.xls'
    openfile = xlrd.open_workbook(filedir, formatting_info=True)
    sheetname = openfile.sheet_by_name(SheetName)

    resList = []
    idx = 0
    for one in sheetname.col_values(0):
        if casename  in one:
            reqBodyData = sheetname.cell(idx,9).value
            respbody = sheetname.cell(idx,11).value

            resList.append((json.loads(reqBodyData),json.loads(respbody)))  #字符串转字典
            # print(respbody)
        idx +=1
    return resList


from xlutils.copy import copy
def set_excelData():
    excelDir = '../data/外卖系统接口测试用例.xls'
    openfile = xlrd.open_workbook(excelDir,formatting_info=True)
    workBookNew = copy(openfile)
    workSheetName = workBookNew.get_sheet(0)
    return workBookNew,workSheetName

if __name__ == '__main__':
    # set_excelData()
    for i in get_ExcelData2('登录模块','Login'):
        print(i)






























